Applying Hierarchies and Drill-Down Analysis in Tableau – Detailed Notes

Course: Tableau Training | Topic: Applying Hierarchies and Drill-Down Analysis | Level: Beginner to Intermediate

Course Link: JustAcademy Tableau Training

Demo Link: Register for a Tableau Course Demo

1. Introduction to Hierarchies and Drill-Down Analysis

Tableau is a data visualization and business intelligence tool that helps users explore data through charts, tables, dashboards, and interactive reports. Hierarchies and drill-down analysis allow users to move from summarized information to more detailed information without building a separate visualization for every level.

For example, a sales manager may initially want to see total sales by country. If sales are low in a particular country, the manager can drill down to regions, states, cities, and individual locations to identify where the problem occurs.

A hierarchy organizes related fields into levels, while drill-down analysis allows users to navigate through those levels. Tableau automatically creates hierarchies for date fields and supports custom hierarchies built from dimensions. Read Tableau's official guide to creating hierarchies.

Learning Objectives

• Understand the meaning and purpose of hierarchies.
• Learn how to create a custom hierarchy in Tableau.
• Organize fields into logical levels.
• Understand drill-down and drill-up analysis.
• Explore geographical, product, organizational, and date hierarchies.
• Use hierarchies in tables, bar charts, and dashboards.
• Analyze data at different levels of detail.
• Identify common errors and apply best practices.

2. What Is a Hierarchy in Tableau?

A hierarchy is an arrangement of related fields in a parent-child structure, progressing from a higher level of detail to a lower level of detail. It helps users navigate related dimensions in a logical order.

For example, a geographical hierarchy may be organized as Country → State → City. A product hierarchy may be Category → Sub-Category → Product Name.

Example: Product Hierarchy

At the Category level, Tableau can display summarized sales for Technology. Drilling down to Sub-Category shows sales for Phones and other subcategories. Drilling down further displays individual products.

Common Types of Hierarchies

• Geographical hierarchy: Country → State → City → Postal Code.
• Product hierarchy: Category → Sub-Category → Product Name.
• Date hierarchy: Year → Quarter → Month → Day.
• Organizational hierarchy: Department → Team → Employee.
• Sales hierarchy: Region → Territory → Sales Representative.
• Financial hierarchy: Business Unit → Account Category → Account.

Benefits of Hierarchies

• Organize related fields in a clear structure.
• Allow users to explore summary and detailed data in one view.
• Reduce the need to create separate worksheets for every level.
• Make dashboards easier to navigate.
• Help identify the source of changes in sales, profit, costs, and other metrics.
• Support interactive analysis for business users.

3. Understanding Drill-Down Analysis

Drill-down analysis is the process of moving from summarized data to a more detailed level. It helps users investigate the individual components that contribute to a total or business result.

Suppose a dashboard shows annual sales of ₹50,00,000. A manager may drill down to quarters, then months, and then days to identify when sales increased or decreased.

Example: Sales Drill-Down Path

Year → Quarter → Month → Day

• Year: Compare sales across years.
• Quarter: Identify the strongest or weakest quarter.
• Month: Discover monthly sales patterns.
• Day: Investigate individual dates.

Business Example

A retail company notices that sales have declined in a particular region. The analyst begins with regional sales, drills down to states, then cities, and finally product categories to identify where the decline is concentrated.

Why Drill-Down Analysis Is Useful

• Investigates the causes behind summarized results.
• Reveals patterns that may not be visible in aggregated data.
• Helps locate underperforming products, cities, teams, or periods.
• Supports more informed business decisions.
• Allows users to explore data without creating an entirely new report for each level.

4. Drill-Down vs. Drill-Up

Drill-down and drill-up are opposite navigation operations within a hierarchy.

In Tableau Desktop and web authoring, hierarchy fields commonly provide plus (+) and minus (−) controls. The plus sign expands the hierarchy to a more detailed level, while the minus sign collapses it to a higher level. The exact interface can vary by version and view. Tableau hierarchy instructions.

5. How to Create a Hierarchy in Tableau

You can create a custom hierarchy by organizing related fields in the Data pane.

Method 1: Drag and Drop

1. Open Tableau Desktop and connect to a dataset.
2. Locate the fields you want to organize in the Data pane.
3. Choose the field that should appear at the highest level, such as Category.
4. Drag the next field, such as Sub-Category, directly onto the first field.
5. When Tableau prompts you, enter a meaningful hierarchy name, such as Product Hierarchy.
6. Click OK to create the hierarchy.
7. Drag additional fields, such as Product Name, into the hierarchy.
8. Reorder the fields by dragging them to the required positions.

Method 2: Create a Hierarchy from a Field Menu

1. Locate a field in the Data pane.
2. Right-click the field.
3. Select Create Hierarchy if the option is available.
4. Enter the hierarchy name.
5. Click OK.
6. Drag other related fields into the new hierarchy.

Example: Creating a Product Hierarchy

Important: When building a hierarchy, arrange fields in the order in which users should explore them. A logical hierarchy makes reports more intuitive.

6. Applying a Hierarchy to a Visualization

Once a hierarchy has been created, you can use it in a worksheet to explore data at different levels.

Steps to Apply a Hierarchy

1. Open a worksheet.
2. Find the hierarchy in the Data pane.
3. Expand the hierarchy if necessary to inspect its fields.
4. Drag the top-level field, such as Category, to Rows.
5. Drag a measure, such as Sales, to Columns.
6. Select an appropriate chart type, such as a bar chart.
7. Use the hierarchy's plus (+) control to drill down.
8. Use the minus (−) control to drill up.
9. Observe how the level of detail and aggregation change.

Example: Sales by Product Category

Start with a bar chart showing SUM(Sales) by Category. When you expand a category, Tableau can display its subcategories. Expanding a subcategory reveals individual products.

The measure is recalculated for the level of detail displayed in the view. At a higher level, sales are summarized by category; at a lower level, sales are broken down by subcategory or product.

7. Working with Date Hierarchies

Tableau automatically provides date hierarchies for date fields, allowing users to explore time-based information at different levels. The available levels depend on the date field and the selected date option.

Common Date Hierarchy

Year → Quarter → Month → Day

Steps to Analyze Dates

1. Identify a date field, such as Order Date.
2. Drag Order Date to Rows or Columns.
3. Choose a suitable date level, such as Year.
4. Drag Sales to the other shelf to create a chart.
5. Use the hierarchy controls to expand to quarters or months.
6. Inspect the trends and compare periods.
7. Drill up when you need to return to a summary.

Example

Suppose annual sales are ₹60,00,000. Drilling down reveals quarterly sales of ₹15,00,000, ₹18,00,000, ₹12,00,000, and ₹15,00,000. The third quarter has the lowest sales, so the analyst can drill down further to examine its monthly results.

Note: Tableau's date fields can offer different date representations, such as discrete date parts and continuous date values. Choose the representation appropriate for your analysis.

8. Geographical Hierarchies and Location Analysis

Geographical hierarchies are useful when analyzing performance by country, state, city, or postal code. They allow users to begin with a broad geographical view and then investigate specific locations.

Example Hierarchy

Country → State → City → Postal Code

Steps

1. Identify the geographical fields in the dataset.
2. Arrange the fields into a logical hierarchy if a suitable hierarchy is not already available.
3. Drag the relevant geographical field to the view.
4. Add Sales or Profit to Color, Size, or another suitable mark property.
5. Drill down to states or cities.
6. Compare locations and identify performance differences.

Business Uses

• Compare revenue across countries and states.
• Identify cities with low profitability.
• Analyze customer distribution by location.
• Investigate regional demand and sales performance.
• Support location-based marketing decisions.

Tip: Check that geographical fields have the correct geographic roles and that Tableau recognizes the locations properly.

9. Product Hierarchies and Product Performance

Product hierarchies help organizations analyze sales and profitability from broad categories down to individual products.

Example Hierarchy

Category → Sub-Category → Product Name

Practical Analysis

1. Build a hierarchy using Category, Sub-Category, and Product Name.
2. Drag Category to Rows and Sales to Columns.
3. Sort categories by sales.
4. Drill down into a category with unusually low sales.
5. Compare its subcategories.
6. Drill down further to identify individual products that contribute to the result.
7. Use Profit alongside Sales to check whether high sales also produce strong profitability.

Example Data

At the Category level, sales are aggregated for each category. Drilling down allows the analyst to inspect the subcategories and individual products contributing to those totals.

10. Drill-Down Analysis with Measures

Hierarchies are generally built from dimensions, such as Region, Category, or Date. Measures such as Sales, Profit, Quantity, and Cost are then used to quantify performance at each level.

Common Measures

• Sales: Measures revenue generated at each hierarchy level.
• Profit: Measures profitability at each level.
• Quantity: Measures the number of units sold.
• Discount: Helps investigate pricing and promotional effects.
• Orders: Measures transaction or order volume.

Example

A company analyzes Profit by Region. If one region performs poorly, the analyst drills down to state and city levels. The same Profit measure can then show which locations contribute most to the regional result.

Important: A measure's aggregation matters. SUM(Profit), AVG(Profit), and a calculated profit margin answer different questions. Validate the aggregation before drawing conclusions.

11. Drill-Down Analysis vs. Filters

Drill-down and filtering can both help users investigate data, but they perform different functions.

Drilling into a hierarchy may also affect which members are visible in the view. A filter, however, is explicitly used to restrict the data included in the analysis. Both techniques can be combined.

12. Creating Interactive Drill-Down Dashboards

A Tableau dashboard can combine several worksheets and let users explore data at different levels. Hierarchies provide a straightforward way to drill into dimensions, while dashboard actions can connect multiple views.

Dashboard Components

• Sales summary chart.
• Regional sales chart.
• Product category chart.
• Date trend chart.
• Relevant filters, such as year or region.
• Clear titles, labels, and tooltips.

Building the Dashboard

1. Create a worksheet showing sales by region.
2. Create a worksheet showing sales by product category and its hierarchy.
3. Create a date-based worksheet for year, quarter, and month analysis.
4. Build a dashboard and add the worksheets.
5. Configure relevant filters and dashboard actions where appropriate.
6. Test drill-down behavior in each view.
7. Verify that labels, titles, and totals remain understandable at different levels.

Advanced Option: Set Actions

For advanced scenarios, Tableau set actions can support more customized drill-down behavior, including expanding only the selected member instead of every member at the next level. This is sometimes called asymmetric drill-down. It requires additional configuration and is different from basic hierarchy expansion.

Official guide: Tableau Set Actions.

13. Practical Project: Sales Hierarchy and Drill-Down Dashboard

Project Objective: Build an interactive dashboard that allows a sales manager to analyze performance from summary level to detailed level.

Required Dataset Fields

• Order Date
• Country
• State
• City
• Category
• Sub-Category
• Product Name
• Sales
• Profit
• Quantity

Project Steps

1. Connect Tableau to the sales dataset.
2. Check field names, data types, geographic roles, and measure aggregations.
3. Create a geographical hierarchy: Country → State → City.
4. Create a product hierarchy: Category → Sub-Category → Product Name.
5. Identify the date hierarchy for Order Date.
6. Create a bar chart for sales by region or country.
7. Use drill-down to inspect states and cities.
8. Create a product sales chart and drill down to product names.
9. Create a date trend chart and explore quarterly and monthly performance.
10. Combine the worksheets into a dashboard.
11. Apply filters and test the hierarchy controls.
12. Write three business insights based on the results.

Questions the Dashboard Should Answer

• Which region generates the highest sales?
• Which state contributes most to its region?
• Which cities have lower-than-expected profit?
• Which product categories perform best?
• Which products contribute most to category sales?
• Which quarter or month shows a sales decline?

Expected Outcome

The completed dashboard should let users move between summary and detailed views, identify performance differences, and investigate the underlying components of sales and profit.

14. Common Mistakes and Troubleshooting

15. Best Practices for Hierarchies and Drill-Down

• Create hierarchies that follow a logical business sequence.
• Use clear names such as Product Hierarchy or Location Hierarchy.
• Start with summary-level views and allow users to explore details when needed.
• Avoid adding unrelated fields to the same hierarchy.
• Check measure aggregation at every level of analysis.
• Use appropriate date levels for the business question.
• Ensure geographical fields are correctly identified.
• Keep dashboards readable and avoid unnecessary visual complexity.
• Use filters together with hierarchies when the analysis needs a restricted data population.
• Test the published dashboard and confirm that users can navigate the views correctly.

16. Interview Questions and Answers

Q1. What is a hierarchy in Tableau?

Answer: A hierarchy organizes related fields into levels, allowing users to navigate from a summary level to more detailed information.

Q2. What is drill-down analysis?

Answer: Drill-down analysis moves from summarized data to a more detailed level to investigate the components behind a result.

Q3. What is the difference between drill-down and drill-up?

Answer: Drill-down moves to a more detailed level, while drill-up returns to a higher, more summarized level.

Q4. How do you create a custom hierarchy?

Answer: Drag one field onto another field in the Data pane, name the hierarchy, and add or reorder additional fields as required.

Q5. Does Tableau automatically create date hierarchies?

Answer: Tableau automatically provides date hierarchies for date fields, allowing analysis at levels such as year, quarter, month, and day.

Q6. Give an example of a product hierarchy.

Answer: Category → Sub-Category → Product Name is a common product hierarchy.

Q7. Can hierarchies be used in dashboards?

Answer: Yes. Worksheets containing hierarchies can be placed in dashboards so users can explore data at different levels.

Q8. What is the difference between a hierarchy and a filter?

Answer: A hierarchy organizes fields into navigable levels, while a filter restricts the data included in the view.

Q9. Why might sales totals appear different after drilling down?

Answer: The view is displaying a different level of detail. Check the aggregation, data model, and filters to ensure the results are interpreted correctly.

Q10. What is asymmetric drill-down?

Answer: Asymmetric drill-down allows a user to expand a selected member without expanding every member at the same hierarchy level. Advanced implementations can use set actions and calculated fields.

Q11. Can you create a custom hierarchy from geographical fields?

Answer: Yes. Fields such as Country, State, and City can be arranged into a custom hierarchy when the data source and fields support the operation.

Q12. What are the benefits of drill-down analysis?

Answer: It helps users investigate summary results, discover patterns, identify causes of performance changes, and make more informed decisions.

17. Practice Exercises

1. Create a product hierarchy using Category, Sub-Category, and Product Name.
2. Create a geographical hierarchy using Country, State, and City.
3. Use a date hierarchy to explore yearly, quarterly, and monthly sales.
4. Build a bar chart showing sales by product category.
5. Drill down to identify the highest-selling product in a category.
6. Analyze profit by region and drill down to individual cities.
7. Combine a date hierarchy with a year filter.
8. Create a dashboard containing product and geographical drill-down views.
9. Compare the same measure at two hierarchy levels and explain the change in detail.
10. Investigate a low-performing category and document the findings.

18. Quick Revision Checklist

• ☐ I understand the meaning of a hierarchy.
• ☐ I can create a custom hierarchy in Tableau.
• ☐ I know how to reorder fields in a hierarchy.
• ☐ I understand drill-down and drill-up.
• ☐ I can use date hierarchies to explore trends.
• ☐ I can use geographical and product hierarchies.
• ☐ I can apply hierarchies to charts and dashboards.
• ☐ I understand how aggregation changes with the level of detail.
• ☐ I can troubleshoot missing controls or unexpected totals.
• ☐ I can explain hierarchy and drill-down concepts in an interview.

19. Conclusion

Hierarchies and drill-down analysis help Tableau users move between summarized and detailed information in a structured way. Custom hierarchies organize related fields, while drill-down and drill-up controls make it easier to explore different levels of data. These features are useful for sales analysis, product performance, geographical reporting, and time-based analysis.

To learn more, visit JustAcademy Tableau Training or Register for a Tableau Course Demo.

Official Tableau References

• Create Hierarchies – Tableau Official Help
• Drilling Down and Up in a Cube Data Source Hierarchy
• Set Actions – Tableau Official Help